﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DBConnection
{
    public class MessagesDAO
    {
        public static List<Message> GetChannelsMessages(string channelName, 
            TwitterDataContext context)
        {
            var messages =
                from m in context.Messages
                join c in context.Channels
                on m.Recipient.Substring(1, m.Recipient.Length - 1) equals c.ChannelID.ToString()
                where c.Name == channelName && m.Recipient[0] == 'c'
                orderby m.Date descending
                select m;
            return messages.ToList();
        }

        public static List<Message> GetUsersMessages(string userName,
            TwitterDataContext context)
        {
            var messages =
                from m in context.Messages
                join u in context.Users
                on m.Recipient.Substring(1, m.Recipient.Length - 1) equals u.UserID.ToString()
                where u.Name == userName && m.Recipient[0] == 'p'
                orderby m.Date descending
                select m;
            return messages.ToList();
        }

        public static List<Message> GetMessagesWithKeywordInAllChanels(string word,
            TwitterDataContext context)
        {   
            var messages =
                from m in context.Messages
                where m.Recipient[0] == 'c' && m.Text.Contains(word)
                orderby m.Date descending
                select m;
            return messages.ToList();
        }

        public static List<Message> GetMessagesWithKeywordInChannel(string word, string channelName,
            TwitterDataContext context)
        {
            // assume that the channel exists
            int channelID = (int)ChannelsDAO.GetChannelID(channelName, context);
            var messages =
                from m in context.Messages
                where m.Recipient[0] == 'c' &&
                m.Recipient.Substring(1, m.Recipient.Length - 1).Equals(channelID.ToString()) &&
                m.Text.Contains(word)
                orderby m.Date descending
                select m;
            return messages.ToList();
        }

        public static void InsertMessageToUser(string senderName, string recipientName, 
            string text, TwitterDataContext context)
        {
            int? senderID = UsersDAO.GetUserID(senderName, context);
            int? recepientID = UsersDAO.GetUserID(recipientName, context);

            Message newMessage = new Message();
            newMessage.Text = text;
            newMessage.SenderID = (int)senderID; // assume that the recipient exists
            newMessage.Recipient = "p" + recepientID;
            newMessage.Date = DateTime.Now;

            context.Messages.InsertOnSubmit(newMessage);
            context.SubmitChanges();
        }

        public static void InsertMessageToChannel(string senderName, string channelName, 
            string text, TwitterDataContext context)
        {
            int? senderID = UsersDAO.GetUserID(senderName, context);
            int? channelID = ChannelsDAO.GetChannelID(channelName, context);

            Message newMessage = new Message();
            newMessage.Text = text;
            newMessage.SenderID = (int)senderID; // assume that the channel exists
            newMessage.Recipient = "c" + channelID;
            newMessage.Date = DateTime.Now;

            context.Messages.InsertOnSubmit(newMessage);
            context.SubmitChanges();
        }

        
    }
}
